package com.dwzlab.servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.dwzlab.test.DSTool;
import com.dwzlab.test.DwzjsonTool;
import com.dwzlab.vo.XUser;
import com.google.gson.Gson;

@SuppressWarnings("unused")
public class DBAjax extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
//		getBeans();
//		getMap();
//		getMaps();
//		getArray();
//		getArrays();
//		getColumn();
//		getScalar();
//		executeDML();
//		response.setContentType("text/json;charset=utf-8");
		response.getWriter().write(new Gson().toJson(DwzjsonTool.newInstance().initDwzjson("200", "OK", "", "", "", "")));
	}

	
	private void executeDML() {
		//insert语句
		String sql = "insert into employee(e_no,e_name) values(?,?)";
//		String sql = "update t_b_employee set e_no = ? where id = ?";
//		String sql = "select e_no,e_name from t_b_employee where id=? and e_no=?";
		try {
			Connection conn = DSTool.newInstance().getConnection();
			System.out.println(conn.getMetaData().getDriverName());
			PreparedStatement pstmt = conn.prepareStatement(sql);
			//SQL Server2005: pstmt.getParameterMetaData()报错
			//MySQL: pstmt.getParameterMetaData()无错
			System.out.println(pstmt.getParameterMetaData());
			pstmt.setString(1, "E-2");
			pstmt.setString(2, "E_1");
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		//注意第二个参数true
//		QueryRunner qr = new QueryRunner(DSTool.newInstance().getDS(), true);
//		try {
////			qr.update(sql,"e1111",10);
//			qr.batch( sql, new Object[][]{{"e1x",10},{"GL00111",15}});
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
	}

	private void getScalar() {
		String sql = "select * from t_b_employee";
		QueryRunner qr = new QueryRunner(DSTool.newInstance().getDS());
		try {
			Object obs = qr.query(sql, new ScalarHandler<Object>("e_no"));
			System.out.println(obs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}


	private void getColumn() {
		String sql = "select * from t_b_employee";
		QueryRunner qr = new QueryRunner(DSTool.newInstance().getDS());
		try {
			List<Object> obs = qr.query(sql, new ColumnListHandler<Object>("e_no"));
			for(Object ob : obs){
				System.out.println(ob);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}


	private void getArrays() {
		String sql = "select e_name,e_no from t_b_employee";
		QueryRunner qr = new QueryRunner(DSTool.newInstance().getDS());
		try {
			List<Object[]> obs = qr.query(sql, new ArrayListHandler());
			for(Object[] os : obs){
				for(Object o : os){
					System.out.println(o);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}


	private void getArray() {
		//默认查第一行
		String sql = "select e_name,e_no from t_b_employee";
		QueryRunner qr = new QueryRunner(DSTool.newInstance().getDS());
		try {
			Object[] obs = qr.query(sql, new ArrayHandler());
			for(Object o : obs){
				System.out.println(o);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}


	private void getMaps() {
		String sql = "select e_name,e_no from t_b_employee";
		QueryRunner qr = new QueryRunner(DSTool.newInstance().getDS());
		try {
			List<Map<String, Object>> maps = qr.query(sql, new MapListHandler());
			for(Map<String, Object> m : maps){
				for(Map.Entry<String, Object> me : m.entrySet()){
					System.out.println(me.getKey()+":"+me.getValue());
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void getMap() {
		String sql = "select e_name,e_no from t_b_employee where id = ?";
		QueryRunner qr = new QueryRunner(DSTool.newInstance().getDS());
		try {
			Map<String, Object> map = qr.query(sql, new MapHandler(),1);
			for(Map.Entry<String, Object> me : map.entrySet()){
				System.out.println(me.getKey()+":"+me.getValue());
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


	private void getBeans() {
		//		String sql = "select id, e_name from t_b_employee";
				String sql = "select id, e_name,ref_d_id from t_b_employee";
		//		QueryRunner qr = new QueryRunner();
				QueryRunner qr = new QueryRunner(DSTool.newInstance().getDS());
		//		BeanHandler<XUser> xuserBean = new BeanHandler<XUser>(XUser.class);
				BeanListHandler<XUser> xuserBeanList = new BeanListHandler<XUser>(XUser.class);
		//		XUser xuser = null;
				List<XUser> xusers = null;
		//		Connection conn = DSTool.newInstance().getConnection();
				try {
		//			xuser =  qr.query(conn, sql, xuserBean, 1);
					xusers = qr.query(sql, xuserBeanList);
				} catch (SQLException e) {
					e.printStackTrace();
				}finally{
		//			try {
		//				DbUtils.close(conn);
		//			} catch (SQLException e) {
		//				e.printStackTrace();
		//			}
				}
				System.out.println(xusers);
	}
}
